import json

from sdv.metadata import Metadata, MultiTableMetadata
from sqlalchemy import text


from tool_utils import get_sql_lite_tool
from tool_utils import get_sql_databaseTool
from tool_utils import  get_single_filed_type_dict
from tool_utils import  update_database_metadata
from tool_utils import  get_database_metadata


def update_new_all_database_metadata(data_source_id, table_name,metadata_json):
    json.loads(metadata_json)
    # 获取 all_table_metadata
    all_table_metadata = get_database_metadata(database_id=data_source_id)

    if all_table_metadata:
       single_metadata  = json.loads(metadata_json)
       all_col_metadata = single_metadata.get('tables').get('table').get('columns')
       for col_name, col_metadata in all_col_metadata.items():
           all_table_metadata.update_column(table_name, col_name, **col_metadata)
       ##save
       update_database_metadata(database_id=data_source_id, metadata=all_table_metadata)

    pass


def single_table_metadata_manage(st):
    st.title("元数据管理")

    # 获取所有数据源
    sql_lite_tool = get_sql_lite_tool()
    with sql_lite_tool.engine.connect() as conn:
        # 获取所有数据源
        data_sources = conn.execute(text("SELECT id, name FROM database_connections")).fetchall()

    if not data_sources:
        st.warning("没有找到任何数据源，请先添加数据源")
        return

    # 数据源选择
    selected_data_source = st.selectbox("选择数据源",
                                        options=[ds[1] for ds in data_sources],
                                        format_func=lambda x: x)

    # 表搜索框
    search_term = st.text_input("搜索表", "")

    # 获取选中数据源的所有表
    if selected_data_source:
        with sql_lite_tool.engine.connect() as conn:
            # 获取选中数据源的id
            data_source_id = \
            conn.execute(text(f"SELECT id FROM database_connections WHERE name = '{selected_data_source}'")).fetchone()[
                0]
            selected_data_source_tool = get_sql_databaseTool(data_source_id)
            tables = selected_data_source_tool.get_table_names()

        # 过滤表列表
        filtered_tables = [t for t in tables if search_term.lower() in t]

        # 显示表列表
        st.subheader("表列表")
        for table in filtered_tables:
            # 创建展开按钮
            with st.expander(f"table_name: {table}", expanded=False):
                # 查看字段按钮
                with sql_lite_tool.engine.connect() as conn:
                    single_table_metadata_list = conn.execute(text(f"""
                        SELECT table_name,table_metadata
                        FROM database_single_table_pattern
                        WHERE database_id = {data_source_id}
                    """)).fetchall()
                    single_table_metadata_dict = {row[0]: row[1] for row in single_table_metadata_list}
                    print(single_table_metadata_dict)
                if True:

                    single_table_metadata = None
                    # 获取表的字段信息
                    if single_table_metadata_dict and single_table_metadata_dict.get(table) is not None:

                        single_table_metadata_json = json.loads(single_table_metadata_dict[table])
                        single_table_metadata = Metadata.load_from_dict(single_table_metadata_json)
                        ## 获取全部的字段
                        columns = get_single_filed_type_dict(single_table_metadata_dict[table])
                    else:
                        columns = {}
                    if columns:
                        ## 获取全部的类型
                        with sql_lite_tool.engine.connect() as connection:
                            # 查询配置表
                            query = text("SELECT ai_type_name, sdv_type_name FROM ai_sdv_datatype_config")
                            result = connection.execute(query)
                            all = result.fetchall()
                            sdtype_list = [row[1] for row in all]
                            sdtype_list.append('categorical')
                            sdtype_list.append('datetime')
                            sdtype_list.append('id')
                            sdtype_list.append('numerical')
                        st.subheader("字段信息")

                        # 创建编辑表单
                        edited_columns = []

                        # 使用columns创建两列布局
                        for item in columns.items():
                            col1, col2 = st.columns([1, 1])

                            with col1:
                                col_name = st.text_input("字段名", value=item[0], disabled=True,
                                                         key=f"col_name_{table}_{item[0]}")
                            with col2:
                                col_type = st.selectbox("字段类型",
                                                        options=sdtype_list,
                                                        index=sdtype_list.index(item[1]),
                                                        key=f"col_type_{table}_{item[0]}")

                            edited_columns.append({
                                'name': col_name,
                                'type': col_type,
                                'original_name': item[0]  # 保存原始字段名用于更新
                            })

                        # 保存修改按钮
                        if st.button("保存修改", key=f"save_{table}"):
                            # 获取所有修改后的字段信息
                            for item in columns.items():
                                col_name = st.session_state[f"col_name_{table}_{item[0]}"]
                                col_type = st.session_state[f"col_type_{table}_{item[0]}"]
                                single_table_metadata.update_column(col_name, 'table', sdtype=col_type)

                            try:
                                # 将metadata转换为字典
                                metadata_dict = single_table_metadata.to_dict()
                                # 将字典转换为JSON字符串
                                metadata_json = json.dumps(metadata_dict)

                                with sql_lite_tool.engine.connect() as conn:
                                    # 使用text()包装SQL语句
                                    query = text("""
                                        UPDATE database_single_table_pattern
                                        SET  table_metadata = :metadata
                                        WHERE table_name = :table_name AND database_id = :db_id
                                    """)
                                    # 使用字典参数绑定
                                    conn.execute(query, {
                                        'metadata': metadata_json,
                                        'table_name': table,
                                        'db_id': data_source_id
                                    })
                                    conn.commit()
                                    ## 如果存在整个库的元数据那么也需要更新
                                    update_new_all_database_metadata(data_source_id, table,metadata_json)

                                st.success(f"表 {table} 的字段信息已更新")
                            except Exception as e:
                                print(e)
                                st.error(f"更新字段失败: {str(e)}")

                    else:
                        st.warning("该表没有字段信息")